<?php
$df = $AppUI->getPref('SHDATEFORMAT');

$status_id = intval( dPgetParam( $_POST, 'status_id', 0 ) );
$customer = intval( dPgetParam( $_POST, 'customer', 0 ) );
$item_type = intval( dPgetParam( $_POST, 'item_type', 0 ) );
$from_date = dPgetParam( $_POST, 'from_date', '' );
$to_date = dPgetParam( $_POST, 'to_date', '' );

$q = new DBQuery();
$q->dropTemp('shipping_types, itemdetails, wunit, qunit, vunit, pitems, container, task_items');
$q->exec();
$q->clear();

$q->createTemp('shipping_types');
$q->addQuery('type_id, name as shipping_type_name');
$q->addTable('md_type');
$q->addWhere("type = 'ShippingType'");
$q->exec();
$q->clear();

$q->createTemp('itemdetails');
$q->addTable('items');
$q->addQuery('items.*, t.name as type_name');
$q->innerJoin('md_type', 't', 't.type_id = items.item_type1');
if ($item_type > 0) {
	$q->addWhere("item_type1 = $item_type");
}
$q->exec();
$q->clear();

$q->createTemp('wunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as wunit_id, unit_name as wunit_name');
$q->exec();
$q->clear();

$q  = new DBQuery;
$q->createTemp('qunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as qunit_id, unit_name as qunit_name');
$q->exec();
$q->clear();

// Volumn Unit
$q  = new DBQuery;
$q->createTemp('vunit');
$q->addTable('md_unit');
$q->addQuery('unit_id as vunit_id, unit_name as vunit_name');
$q->exec();
$q->clear();

// Container type
$q  = new DBQuery;
$q->createTemp('container');
$q->addTable('md_type');
$q->addQuery('type_id as container_id, name as container_name');
$q->exec();
$q->clear();

$q->createTemp('pitems');
$q->addTable('project_items');
$q->innerJoin('itemdetails', 'i', 'pitem_item_id = i.item_id');
$q->leftJoin('wunit', 'wu', 'i.item_weight_unit = wu.wunit_id');
$q->leftJoin('qunit', 'qu', 'i.item_quantity_unit = qu.qunit_id');
$q->leftJoin('vunit', 'vu', 'i.item_volume_unit = vu.vunit_id');
$q->leftJoin('container', 'con', 'pitem_container = con.container_id');
$q->leftJoin('md_currency', 'currency', 'pitem_currency = currency.currency_id');
$q->addQuery('pitem_id, pitem_project,pitem_item_id,pitem_BL_AW,pitem_invoice_no,pitem_currency,pitem_invoice_amount,pitem_PO_no,pitem_CO_no,pitem_contract_no,pitem_quantity,pitem_weight,pitem_volume,pitem_container,pitem_package,pitem_stamp_time,DATE_FORMAT(pitem_stamp_date, \'%Y-%m-%dT%T.000\') as pitem_stamp_date,pitem_customs_dec_no,DATE_FORMAT(pitem_custom_date, \'%Y-%m-%dT%T.000\') as pitem_custom_date,DATE_FORMAT(pitem_ETA_date, \'%Y-%m-%dT%T.000\') as pitem_ETA_date,DATE_FORMAT(pitem_delivery_date, \'%Y-%m-%dT%T.000\') as pitem_delivery_date,pitem_delivery_location,pitem_inspection,pitem_unloading,pitem_remarks, ' .
		'i.item_name, i.item_code, wu.wunit_name as weight_name, qu.qunit_name as quantity_name, vu.vunit_name as volume_name, container_name, currency_name, currency_abbreviation');
$q->exec();
$q->clear();

$q->addTable('projects');
//left join to sure load all project
$q->leftJoin('pitems', 'pi', 'pitem_project = project_id');
//$q->addJoin('shipping_types', 'stypes', 'projects.project_shipping_type = stypes.type_id');
//$q->innerJoin('companies', 'c', 'company_id = project_company');
//$q->addJoin('suppliers', 'sup', 'sup_id = project_shipper');
//$q->innerJoin('md_type', 't', 'project_rtype = t.type_id');
//$q->innerJoin('md_status', 's', 'project_rstatus = s.status_id');
//$q->addQuery('project_id, project_code, project_name, project_iso_code, shipping_type_name, t.name as type, s.status_name as status, company_name, ' .
//			' pi.*, sup.sup_name as shipper_name');
$q->addQuery('project_id, project_code, project_name, project_iso_code, ' .
			' pi.* ');
$q->addOrder('project_id');
if ($customer > 0) {
	$q->addWhere("project_company = $customer");
}
if ($status_id > 0) {
	$q->addWhere("project_rstatus = $status_id");
}
if ($from_date) {
	$q->addWhere("project_date >= $from_date");
}
if ($to_date) {
	$q->addWhere("project_date <= $to_date");
}
$result = $q->loadList();
//showFVar($result); exit;

$q->clear();

//load task fee of references, only get task fee > 0
$q->createTemp('task_items');
$q->addTable('tasks');
$q->innerJoin('itemdetails', 'i', 'task_item = i.item_id');
$q->exec();
$q->clear();

$q->addTable('projects');
$q->innerJoin('task_items', 't', 'task_project = project_id');
$q->addWhere("task_fee > 0");
if ($customer > 0) {
	$q->addWhere("project_company = $customer");
}
if ($status_id > 0) {
	$q->addWhere("project_rstatus = $status_id");
}
if ($from_date) {
	$q->addWhere("project_date >= $from_date");
}
if ($to_date) {
	$q->addWhere("project_date <= $to_date");
}
$q->addQuery('project_id, task_id, item_name, task_fee');
$q->addOrder('project_id');
$taskFees = $q->loadList();
//showFVar($taskFees);
//build taskFees to hash fees
$hashFees = array();
$lastProject = 0;
$size_del_col =0;
$current_size = 0;
foreach($taskFees as $f) {
	if ($lastProject != $f['project_id']) {
		$lastProject = $f['project_id'];
		$hashFees[$lastProject] = array();
		$current_size = 1;
	}
	else{
		$current_size = $current_size +1;
	}
	
	if($current_size > $size_del_col){
		$size_del_col = $current_size;
	}		
	$hashFees[$lastProject][] = $f;
}
//showFVar($hashFees);
//echo 'size_del_col: '. $size_del_col;

//exit;
include_once(DP_BASE_DIR."/modules/reports/xtpl/xtemplate.class.php");

$xtpl = new XTemplate(DP_BASE_DIR."/modules/reports/HaddlingFee.xtpl");
$size_ref = count($result);
//$size_ref = 3;
// parse block1
/*
$xtpl->parse('main.block1');

for($i=0; $i<$size-1; $i++) {
	$xtpl->assign('DATA',$result[$i]);
	$xtpl->parse('main.row');
}
$xtpl->assign('DATA',$result[$size-1]);
$xtpl->parse('main.lastrow');
*/
// col
for($i=1; $i<=$size_del_col; $i++) {
	$xtpl->assign('INDEX',$i);
	$xtpl->parse('main.column');
}
// row
$tmp_project_id = 0;
for($j=0; $j < $size_ref-1; $j++) {
	$ref_line = $result[$j];
	// dycol
	if($tmp_project_id != $ref_line['project_id']){
		$tmp_project_id = $ref_line['project_id'];
		$fees = $hashFees[$tmp_project_id];
		$size_fee = count($fees);
		//echo 'fee'; showFVar($fees);
		$mis = 	$size_del_col - $size_fee;		 	
		if($fees!= null && count($fees)>0){
			for($f=0;$f< $size_fee;$f++){							
				$xtpl->assign('COL',$fees[$f]);								
			//	echo 'feef'; showFVar($fees[$f]);
				$xtpl->parse('main.row.dycol');				
			}
		}		
		for($l= 1; $l<=$mis;$l++){
			$xtpl->parse('main.row.dycolemty');
		}					
	}
	else{
		for($l= 1; $l<=$size_del_col;$l++){
			$xtpl->parse('main.row.dycolemty');
		}
	}
	$xtpl->assign('DATA',$ref_line);
	$xtpl->assign('pitem_stamp_date',replace($ref_line['pitem_stamp_date']));
	$xtpl->assign('pitem_custom_date',replace($ref_line['pitem_custom_date']));
	$xtpl->assign('pitem_ETA_date',replace($ref_line['pitem_ETA_date']));
	$xtpl->assign('pitem_delivery_date',replace($ref_line['pitem_delivery_date']));
	$xtpl->parse('main.row');	
}

// last row

$ref_line = $result[$size_ref-1];
	// dycol
	if($tmp_project_id != $ref_line['project_id']){
		$tmp_project_id = $ref_line['project_id'];
		$fees = $hashFees[$tmp_project_id];
		$size_fee = count($fees);
		$mis = 	$size_del_col - $size_fee;		 	
		if($fees!= null && count($fees)>0){
			for($f=0;$f< $size_fee;$f++){							
				$xtpl->assign('COL',$fees[$f]);								
			//	echo 'feef'; showFVar($fees[$f]);
				$xtpl->parse('main.lastrow.dycol');				
			}
		}		
		for($l= 1; $l<=$mis;$l++){
			$xtpl->parse('main.lastrow.dycolemty');
		}					
	}
	else{
		for($l= 1; $l<=$size_del_col;$l++){
			$xtpl->parse('main.lastrow.dycolemty');
		}
	}
	$xtpl->assign('DATA',$ref_line);
	$xtpl->assign('pitem_stamp_date',replace($ref_line['pitem_stamp_date']));
	$xtpl->assign('pitem_custom_date',replace($ref_line['pitem_custom_date']));
	$xtpl->assign('pitem_ETA_date',replace($ref_line['pitem_ETA_date']));
	$xtpl->assign('pitem_delivery_date',replace($ref_line['pitem_delivery_date']));
	$xtpl->parse('main.lastrow');
// end last roe
function replace($date){
	if($date != null){		
		return 	'<Data ss:Type="DateTime">'.$date.'</Data>';
	}
	else{			
		return $date;
	}	  
}
//exit;
// simple replace
$xtpl->assign('FROMDATE', $from_date);
$xtpl->assign('TODATE', $to_date);
$xtpl->assign('SIZE_ROW', $size_ref+8);
$xtpl->assign('SIZE_COL', 50+($size_del_col*2));
$xtpl->assign('SIZE_DE_COL', ($size_del_col*2) -1);
$xtpl->parse('main');
$out = $xtpl->out('main');

$temp_dir = dPgetConfig( 'root_dir' )."/files/temp";
if ($fp = fopen( "$temp_dir/temp$AppUI->user_id.xls", 'wb' )) {
	fwrite( $fp, $out);
	fclose( $fp );
	header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
	header("Content-Disposition: inline; filename=\"HanddlingFeeReport.xls\"");
	readfile("$temp_dir/temp$AppUI->user_id.xls");
}

?>
